OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Locking examples

The following example illustrates how the end of a transaction affects OpenEdge and MSS data source locks differently:


DO TRANSACTION: FIND customer WHERE cust-num = 10. UPDATE customer. END.

When you access an OpenEdge database with this procedure, the customer record is share-locked when the first transaction ends.

When you access an MSS data source with the DataServer, the customer record is released when the first transaction ends.

This example illustrates how OpenEdge and MSS data source share locks differ in scope and duration:

FIND customer WHERE cust-num = 10 SHARE-LOCK NO-WAIT NO-ERROR. IF AVAILABLE customer THEN DO: DISPLAY customer. PROMPT-FOR customer. tx: DO TRANSACTION ON ERROR UNDO tx, RETRY tx: FIND customer WHERE cust-num = 10 EXCLUSIVE-LOCK NO-WAIT NO-ERROR. IF LOCKED customer THEN DO: Message "customer locked - retrying". UNDO tx, RETRY tx. END. ELSE DO: ASSIGN customer. END. END. END.

FIND customer WHERE cust-num = 10 SHARE-LOCK NO-WAIT NO-ERROR.
IF AVAILABLE customer THEN DO:
  DISPLAY customer.
  PROMPT-FOR customer.
  
  tx:
  DO TRANSACTION ON ERROR UNDO tx, RETRY tx:
    FIND customer WHERE cust-num = 10 EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
    IF LOCKED customer THEN DO:
      Message "customer locked - retrying".
      UNDO tx, RETRY tx.
      END.
    ELSE DO:
      ASSIGN customer.
      END.
  END.
END.

In this example, the first record is only share-locked within the MS SQL Server data source if the isolation level setting requires it. (Recall that a SHARE–LOCK specified in a 4GL statement is ignored by the DataServer.) As a result, if the isolation level is read uncommitted or read committed, the record might be updated by another client before the second FIND statement executes. This could cause the record that the second FIND statement fetches to be different from the record fetched by the first FIND statement. This procedure might cause updated information to be lost because the procedure applies updates based on the first find of the record, and these updates will overwrite the values from the second find of the record.

Using the DataServer to access a MS SQL Server database ensures that locks are upgraded in the data source in the same way as in an OpenEdge database. For example, the following procedure causes the same behavior whether you access an OpenEdge database or an MSS data source:


FIND customer WHERE cust-num = 10. DISPLAY customer. PROMPT-FOR customer. DO TRANSACTION: ASSIGN customer. END.

The record is share-locked when it is fetched. The DataServer upgrades the share lock to an exclusive lock inside the transaction by locking the record, reading it, and checking whether the record has changed since it was first fetched. If it has changed, the lock upgrade fails and you receive an error message.

You try to update a record when another user is reading it (it is share-locked). This also depends on the isolation level.

You try to read or update a record when another user is updating it (it is exclusive-locked).

When this happens, OpenEdge uses a time-out loop, checking periodically to see whether the record is available. You can choose Cancel at any time to abort the request.

The MSS data source notifies the DataServer if it cannot perform a requested operation within a given period of time. Under unusual system or network loads, the DataServer might receive notification that a request has not been completed. In this case, it returns a message that the record the request was accessing is locked, even though no other user has a lock on the record.

One type of locking behavior that you might encounter is a deadlock, or “deadly embrace.” A deadlock occurs when two users want to access each other’s table, page, or record, and the table, page, or record that they want either has an exclusive lock on it, or one of the users needs to put an exclusive lock on it. Neither table, page, or record will give up its lock until the other table, page, or record is available. When an MSS data source detects this situation:

The data source kills the transaction that has accumulated the least amount of CPU time and releases the table, page, or record for the other user.

The Progress 4GL displays a message that the transaction was killed.

The system responds as if you had chosen Cancel.

For details on how OpenEdge database locks work, see OpenEdge Development: Progress 4GL Handbook . See ODBC and MS SQL Server documentation for more information about locks in SQL Server.